from flask import request, jsonify, render_template
from database import get_db_connection
from datetime import datetime
import random

def register_word_routes(app):
    @app.route('/words', methods=['GET'])
    def get_words():
        conn = get_db_connection()
        c = conn.cursor()
        c.execute('SELECT * FROM words')
        words = c.fetchall()
        conn.close()
        return jsonify([{'id': word[0], 'chinese': word[1], 'hiragana': word[2], 'category': word[3], 'meaning': word[4]} for word in words])

    @app.route('/words', methods=['POST'])
    def add_word():
        data = request.get_json()
        chinese = data.get('chinese')
        hiragana = data.get('hiragana')
        category = data.get('category')
        meaning = data.get('meaning')
        if not chinese or not hiragana or not category or not meaning:
            return jsonify({'error': 'Chinese, Hiragana, Category and Meaning are required'}), 400
        add_time = datetime.now()
        last_test_time = None
        conn = get_db_connection()
        c = conn.cursor()
        c.execute('INSERT INTO words (chinese, hiragana, category, meaning, add_time, last_test_time) VALUES (?, ?, ?, ?, ?, ?)', (chinese, hiragana, category, meaning, add_time, last_test_time))
        conn.commit()
        new_word_id = c.lastrowid
        conn.close()
        return jsonify({'id': new_word_id, 'chinese': chinese, 'hiragana': hiragana, 'category': category, 'meaning': meaning}), 201

    @app.route('/words/<int:word_id>', methods=['PUT'])
    def update_word(word_id):
        console.log("update_word")
        data = request.get_json()
        chinese = data.get('chinese')
        hiragana = data.get('hiragana')
        category = data.get('category')
        meaning = data.get('meaning')
        if not chinese or not hiragana or not category or not meaning:
            return jsonify({'error': 'Chinese, Hiragana, Category and Meaning are required'}), 400
        conn = get_db_connection()
        c = conn.cursor()
        c.execute('UPDATE words SET chinese = ?, hiragana = ?, category = ?, meaning = ? WHERE id = ?', (chinese, hiragana, category, meaning, word_id))
        conn.commit()
        conn.close()
        return jsonify({'message': 'Word updated successfully'})

    @app.route('/words/<int:word_id>', methods=['DELETE'])
    def delete_word(word_id):
        conn = get_db_connection()
        c = conn.cursor()
        c.execute('DELETE FROM words WHERE id = ?', (word_id,))
        conn.commit()
        conn.close()
        return jsonify({'message': 'Word deleted successfully'})

    @app.route('/addword', methods=['GET'])
    def show_add_word_page():
        category = request.args.get('category', '')
        meaning = request.args.get('meaning', '')
        return render_template('addword.html', 
                                category=category, 
                                meaning=meaning,)

    @app.route('/addword', methods=['POST'])
    def process_add_word():
        chinese = request.form.get('chinese')
        hiragana = request.form.get('hiragana')
        category = request.form.get('category')
        meaning = request.form.get('meaning')
        add_time = datetime.now()
        last_test_time = None

        if not chinese or not hiragana or not category or not meaning:
            error_message = '汉字、平假名、分类和中文释义均为必填项'
            return render_template('addword.html', 
                                   chinese=chinese, 
                                   hiragana=hiragana, 
                                   category=category, 
                                   meaning=meaning,
                                   error_message=error_message)

        conn = get_db_connection()
        c = conn.cursor()
        try:
            c.execute('INSERT INTO words (chinese, hiragana, category, meaning, add_time, last_test_time) VALUES (?, ?, ?, ?, ?, ?)', (chinese, hiragana, category, meaning, add_time, last_test_time))
            conn.commit()
            new_word_id = c.lastrowid
            conn.close()
            return render_template('addword.html', category=category, )  # 重置页面
        except Exception as e:
            conn.close()
            error_message = f'添加单词时出错: {str(e)}'
            return render_template('addword.html', 
                                   chinese=chinese, 
                                   hiragana=hiragana, 
                                   category=category, 
                                   meaning=meaning,
                                   error_message=error_message)

    @app.route('/word_list', methods=['GET'])
    def word_list():
        conn = get_db_connection()
        c = conn.cursor()

        # 获取筛选参数
        chinese = request.args.get('chinese', '')
        hiragana = request.args.get('hiragana', '')
        category = request.args.get('category', '')
        meaning = request.args.get('meaning', '')
        add_time = request.args.get('add_time', '')
        last_test_time = request.args.get('last_test_time', '')

        # 构建查询语句
        query = 'SELECT * FROM words WHERE 1=1'
        params = []

        if chinese:
            query += ' AND chinese LIKE ?'
            params.append(f'%{chinese}%')
        if hiragana:
            query += ' AND hiragana LIKE ?'
            params.append(f'%{hiragana}%')
        if category:
            query += ' AND category LIKE ?'
            params.append(f'%{category}%')
        if meaning:
            query += ' AND meaning LIKE ?'
            params.append(f'%{meaning}%')
        if add_time:
            query += ' AND DATE(add_time) LIKE ?'
            params.append(f'%{add_time}%')
        if last_test_time:
            query += ' AND DATE(last_test_time) LIKE ?'
            params.append(f'%{last_test_time}%')

        c.execute(query, params)
        words = c.fetchall()
        conn.close()

        # 转换时间戳为日期字符串
        converted_words = []
        for word in words:
            word_list = list(word)
            if word_list[5]:
                if isinstance(word_list[5], datetime):  # add_time
                    word_list[5] = word_list[5].strftime('%Y-%m-%d')
                elif isinstance(word_list[5], int):  # 处理 SQLite 存储的时间戳
                    word_list[5] = datetime.fromtimestamp(word_list[5]).strftime('%Y-%m-%d')
            if word_list[6]:
                if isinstance(word_list[6], datetime):  # last_test_time
                    word_list[6] = word_list[6].strftime('%Y-%m-%d')
                elif isinstance(word_list[6], int):  # 处理 SQLite 存储的时间戳
                    word_list[6] = datetime.fromtimestamp(word_list[6]).strftime('%Y-%m-%d')
            converted_words.append(tuple(word_list))

        return render_template('word_list.html', words=converted_words, chinese=chinese, hiragana=hiragana, category=category, meaning=meaning,
                               add_time=add_time, last_test_time=last_test_time)

    @app.route('/update_word/<int:word_id>', methods=['GET'])
    def show_update_word_page(word_id):
        conn = get_db_connection()
        c = conn.cursor()
        c.execute('SELECT * FROM words WHERE id =?', (word_id,))
        word = c.fetchone()
        conn.close()
        if word:
            word_obj = list(word) # 转换为对象
            return render_template('edit_word.html', 
                                    word_id=word_obj[0], 
                                    chinese=word_obj[1], 
                                   hiragana=word_obj[2], 
                                   category=word_obj[3], 
                                   meaning=word_obj[4],)
        else:
            return 'Word not found', 404
    
    @app.route('/update_word', methods=['POST'])
    def process_update_word():
        # print("update_word", request.form) # 打印表单数据 (for debug)
        word_id = request.form.get('word_id')
        chinese = request.form.get('chinese')
        hiragana = request.form.get('hiragana')
        category = request.form.get('category')
        meaning = request.form.get('meaning')
        if not chinese or not hiragana or not category or not meaning:
            return jsonify({'error': 'Chinese, Hiragana, Category and Meaning are required'}), 400
        conn = get_db_connection()
        c = conn.cursor()
        c.execute('UPDATE words SET chinese = ?, hiragana = ?, category = ?, meaning = ? WHERE id = ?', (chinese, hiragana, category, meaning, word_id))
        conn.commit()
        conn.close()
        return jsonify({'message': 'Word updated successfully'})

def get_random_word():
    conn = get_db_connection()
    c = conn.cursor()
    c.execute('SELECT id, chinese, hiragana, category, meaning FROM words')
    words = c.fetchall()
    conn.close()
    if not words:
        return None
    return random.choice(words)